﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using System.Configuration;
using System.Web.Services;

namespace IphoneService
{
    public partial class _Default : System.Web.UI.Page
    {
        public string ConStr = "";

        protected void Page_Load(object sender, EventArgs e)
        {
            ConStr = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;

            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "text/plain";

            MikroV14Sql instance = new MikroV14Sql();
            //switch (ConfigurationManager.AppSettings["ProgramTipi"])
            //{
            //    case "0":
            //        //instance = new WorkSql();
            //        break;
            //    case "1":
            //        instance = new MikroV12Sql();
            //        break;
            //    case "2":
            //        instance = new MikroV14Sql();
            //        break;
            //}

            if (Request.QueryString.Count > 0)
            {
                switch (Request.QueryString["islem"])
                {
                    case "0":
                        Response.Write(ConvertToJson(instance.AnaGrupSql));
                        break;
                    case "1":
                        Response.Write(ConvertToJson(instance.BankaSql));
                        break;
                    case "2":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.BarkodSql));
                        break;
                    case "3":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.BirimSql));
                        break;
                    case "4":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.CariAdresSql));
                        break;
                    case "5":
                        Response.Write(ConvertToJson(instance.CariFoySql));
                        break;
                    case "6":
                        Response.Write(ConvertToJson(instance.CariKampanyaSql));
                        break;
                    case "7":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.CariSql));
                        break;
                    case "8":
                        Response.Write(ConvertToJson(instance.DepoSql));
                        break;
                    case "9":
                        Response.Write(ConvertToJson(instance.DovizSql));
                        break;
                    case "10":
                        Response.Write(ConvertToJson(instance.KampanyaSql));
                        break;
                    case "11":
                        Response.Write(ConvertToJson(instance.RouteSql));
                        break;
                    case "12":
                        Response.Write(ConvertToJson(instance.SaticiSql));
                        break;
                    case "13":
                        Response.Write(ConvertToJson(instance.SatisSartiListesi));
                        break;
                    case "14":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.StokDepoMiktarSql));
                        break;
                    case "15":
                        Response.Write(ConvertToJson(instance.StokKampanyaSql));
                        break;
                    case "16":
                        Response.Write(ConvertToJson(instance.StokMiktarSql));
                        break;
                    case "17":
                        instance.Filter = Request.QueryString["Tarih"];
                        Response.Write(ConvertToJson(instance.StokSql));
                        break;
                    case "18":
                        Response.Write(ConvertToJson(instance.IskontoMatrisleri));
                        break;
                    case "19":
                        Response.Write(ConvertToJson(instance.VergiOranlari));
                        break;
                    case "20":
                        Response.Write(ConvertToJson(instance.AyarSql));
                        break;
                    case "21":
                        Response.Write(ConvertToJson(instance.SrmMerkezSql));
                        break;
                    case "Import":
                        Response.Write(SaveInvoiceHeader(Request.QueryString["Value"].ToString()));
                        break;
                    case "Cari":
                        instance.CariEkle(Request.QueryString["Value"].ToString(), ConStr);
                        break;
                    case "Stok":
                        instance.StokEkle(Request.QueryString["Value"].ToString(), ConStr);
                        break;
                    case "Iade":
                        SaveStockMovement(Request.QueryString["Value"].ToString());
                        break;
                    default:
                        break;
                }
            }
            Response.End();
        }

        public void LocationKaydet(string Location)
        {
            string[] dizi = Location.Split('%');

            LocationKaydet(dizi[0], dizi[1], dizi[2], dizi[3].Replace("3A", ":").Replace("2F", "/"));
        }

        void LocationKaydet(string TerminalID, string Lati, string Longi, string Tarih)
        {
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("Insert into Location values(@Terminal,@Lati,@Longi,@Tarih)", cn))
                {
                    cmd.Parameters.Add("Terminal", int.Parse(TerminalID));
                    cmd.Parameters.Add("Lati", Lati);
                    cmd.Parameters.Add("Longi", Longi);
                    cmd.Parameters.Add("Tarih", DateTime.Parse(Tarih));
                    cmd.ExecuteNonQuery();
                }
                cn.Close();
            }
        }

        private string ConvertToJson(string sql)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                cn.Open();
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int fieldcount = reader.FieldCount;
                                object[] values = new object[fieldcount];
                                reader.GetValues(values);
                                jsonWriter.WriteStartObject();

                                for (int index = 0; index < fieldcount; index++)
                                {
                                    jsonWriter.WritePropertyName("C" + index);
                                    jsonWriter.WriteValue(values[index]);
                                }
                                jsonWriter.WriteEndObject();
                            }
                            reader.Close();
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    cn.Close();
                }
            }
            string result = "[" + sb.ToString() + "]";
            result = result.Replace("}", "},");
            result = result.Replace("},]", "}]");
            return result;
        }

        public string SaveInvoiceHeader(string data)
        {
            string header = data.Split('%')[0];
            string lines = data.Split('%')[1];
            return SaveInvoiceHeader(lines.Split('|'), header.Split(';'));
        }
        public void SaveStockMovement(string Header)
        {
            SaveStockMovement(Header.Split('%'));
        }

        string SaveInvoiceHeader(string[] detailLines, string[] headerLines)
        {
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                cn.Open();
                SqlTransaction trn = cn.BeginTransaction();
                SqlCommand dcmd = new SqlCommand("INVOICE_DETAILS_INS", cn);
                SqlCommand hcmd = new SqlCommand("INVOICE_HEADER_INS", cn);
                hcmd.CommandType = CommandType.StoredProcedure;
                dcmd.CommandType = CommandType.StoredProcedure;
                hcmd.Transaction = trn;
                dcmd.Transaction = trn;

                SqlCommand cmd = new SqlCommand("SELECT     PARAMETER_VALUE FROM         DEVICE_PARAMETERS WHERE     (PARAMETER_CODE = 'Store') AND (DEVICE_ID = " + headerLines[0] + ")", cn);
                cmd.Transaction = trn;
                string depoKod = cmd.ExecuteScalar().ToString();
                //string depoKod = dr[0].ToString();
                //dr.Close();
                SqlCommand silcmd = new SqlCommand("SELECT  TOP 1 isnull(ID,-1) FROM INVOICE_HEADER WHERE (TERMINAL_ID = @TERMINAL_ID) AND (DOCUMENT_TYPE = @DOCUMENT_TYPE) AND (DOCUMENT_SERIES = @DOCUMENT_SERIES)", cn);
                silcmd.Transaction = trn;
                silcmd.Parameters.Add("@TERMINAL_ID", SqlDbType.Int).Value = int.Parse(headerLines[0]);
                silcmd.Parameters.Add("@DOCUMENT_TYPE", SqlDbType.TinyInt).Value = byte.Parse(headerLines[1]);
                silcmd.Parameters.Add("@DOCUMENT_SERIES", SqlDbType.Int).Value = int.Parse(headerLines[2]);

                int headerID = Convert.ToInt32(silcmd.ExecuteScalar());
                if (headerID != -1)
                {
                    silcmd.Parameters.Clear();
                    silcmd.CommandText = "DELETE FROM INVOICE_HEADER WHERE ID=@id";
                    silcmd.Parameters.Add("@id", SqlDbType.Int).Value = headerID;
                    silcmd.ExecuteNonQuery();
                    silcmd.CommandText = "DELETE FROM INVOICE_DETAILS WHERE HEADER_ID=@id";
                    silcmd.ExecuteNonQuery();
                }

                hcmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
                hcmd.Parameters.Add("@CREATE_DATE", SqlDbType.DateTime).Value = DateTime.Now;
                hcmd.Parameters.Add("@UPDATE_DATE", SqlDbType.DateTime).Value = DateTime.Now;
                hcmd.Parameters.Add("@SAVED_FLAG", SqlDbType.Bit).Value = false;
                hcmd.Parameters.Add("@SENT_FLAG", SqlDbType.Bit).Value = false;
                hcmd.Parameters.Add("@TERMINAL_ID", SqlDbType.Int).Value = int.Parse(headerLines[0]);
                hcmd.Parameters.Add("@DOCUMENT_TYPE", SqlDbType.TinyInt).Value = byte.Parse(headerLines[1]);
                hcmd.Parameters.Add("@DOCUMENT_SERIES", SqlDbType.Int).Value = int.Parse(headerLines[2]);
                hcmd.Parameters.Add("@DOCUMENT_SERIAL", SqlDbType.NVarChar, 6).Value = headerLines[3];
                hcmd.Parameters.Add("@CUSTOMER_CODE", SqlDbType.NVarChar, 25).Value = headerLines[4];
                hcmd.Parameters.Add("@DOCUMENT_DATE", SqlDbType.DateTime).Value = DateTime.Parse(headerLines[5]);
                hcmd.Parameters.Add("@SUBTOTAL", SqlDbType.Float).Value = decimal.Parse((headerLines[6]));
                hcmd.Parameters.Add("@VAT_TOTAL", SqlDbType.Float).Value = decimal.Parse((headerLines[7]));
                hcmd.Parameters.Add("@DISCOUNT_TOTAL", SqlDbType.Float).Value = decimal.Parse((headerLines[8]));
                hcmd.Parameters.Add("@GENERAL_TOTAL", SqlDbType.Float).Value = decimal.Parse((headerLines[9]));
                hcmd.Parameters.Add("@INVOICE_SERIES", SqlDbType.NVarChar, 6).Value = headerLines[10];
                hcmd.Parameters.Add("@INVOICE_NUMBER", SqlDbType.Int).Value = int.Parse(headerLines[11]);
                hcmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar, 4000).Value = headerLines[12]; //vade gün
                hcmd.Parameters.Add("@STORE_CODE", SqlDbType.NVarChar, 25).Value = depoKod;
                hcmd.Parameters.Add("@RESPONSIBILITY_CODE", SqlDbType.NVarChar, 25).Value = headerLines[13]; // srm merkezi
                hcmd.Parameters.Add("@EXPLANATION", SqlDbType.NVarChar, 250).Value = headerLines[14]; //sevk tarihi
                hcmd.Parameters.Add("@PRINTED", SqlDbType.TinyInt).Value = bool.Parse("true");
                hcmd.Parameters.Add("@TEVKIFAT", SqlDbType.Float).Value = decimal.Parse(("0"));
                hcmd.Parameters.Add("@OTV", SqlDbType.Float).Value = decimal.Parse(("0"));
                hcmd.Parameters.Add("@BELGE_NO", SqlDbType.VarChar).Value = headerLines[16];
                hcmd.ExecuteNonQuery();

                int header_id = Convert.ToInt32(hcmd.Parameters["@ID"].Value);

                if (headerLines[1] != "30" && headerLines[1] != "31") // tahsilat değilse
                {
                    dcmd.Parameters.Add("@HEADER_ID", SqlDbType.Int).Value = header_id;
                    dcmd.Parameters.Add("@LINE_NUMBER", SqlDbType.SmallInt);
                    dcmd.Parameters.Add("@PROMO_ID", SqlDbType.Int);
                    dcmd.Parameters.Add("@PRODUCT_CODE", SqlDbType.NVarChar, 55);
                    dcmd.Parameters.Add("@AMOUNT", SqlDbType.Float);
                    dcmd.Parameters.Add("@PRICE", SqlDbType.Float);
                    dcmd.Parameters.Add("@DISCOUNT_RATE_1", SqlDbType.Float);
                    dcmd.Parameters.Add("@DISCOUNT_RATE_2", SqlDbType.Float);
                    dcmd.Parameters.Add("@DISCOUNT_RATE_3", SqlDbType.Float);
                    dcmd.Parameters.Add("@DISCOUNT_RATE_4", SqlDbType.Float);
                    dcmd.Parameters.Add("@DISCOUNT_RATE_5", SqlDbType.Float);
                    dcmd.Parameters.Add("@VAT_RATE", SqlDbType.Int);
                    dcmd.Parameters.Add("@DISCOUNT_TOTAL", SqlDbType.Float);
                    dcmd.Parameters.Add("@LINE_TOTAL", SqlDbType.Float);
                    dcmd.Parameters.Add("@LINE_NET_TOTAL", SqlDbType.Decimal);
                    dcmd.Parameters.Add("@CURRENCY", SqlDbType.Int);
                    dcmd.Parameters.Add("@CURRENCY_RATE", SqlDbType.Float);
                    dcmd.Parameters.Add("@TEVKIFAT_TURU", SqlDbType.Int);
                    dcmd.Parameters.Add("@TEVKIFAT_TUTARI", SqlDbType.Float);
                    dcmd.Parameters.Add("@OTV_TUTARI", SqlDbType.Float);

                    string[] alanlar;
                    for (int i = 0; i < detailLines.Length; i++)
                    {
                        alanlar = detailLines[i].Split(';');
                        if (alanlar.Length > 14)
                        {
                            dcmd.Parameters["@LINE_NUMBER"].Value = i;
                            dcmd.Parameters["@PROMO_ID"].Value = int.Parse(alanlar[0]);
                            dcmd.Parameters["@PRODUCT_CODE"].Value = alanlar[1];
                            dcmd.Parameters["@AMOUNT"].Value = decimal.Parse((alanlar[2]));
                            dcmd.Parameters["@PRICE"].Value = decimal.Parse((alanlar[3]).Replace('.', ','));
                            dcmd.Parameters["@DISCOUNT_RATE_1"].Value = decimal.Parse((alanlar[4]));
                            dcmd.Parameters["@DISCOUNT_RATE_2"].Value = decimal.Parse((alanlar[5]));
                            dcmd.Parameters["@DISCOUNT_RATE_3"].Value = decimal.Parse((alanlar[6]));
                            dcmd.Parameters["@DISCOUNT_RATE_4"].Value = decimal.Parse((alanlar[7]));
                            dcmd.Parameters["@DISCOUNT_RATE_5"].Value = decimal.Parse((alanlar[8]));
                            dcmd.Parameters["@VAT_RATE"].Value = int.Parse(alanlar[9]);
                            dcmd.Parameters["@DISCOUNT_TOTAL"].Value = decimal.Parse((alanlar[10]));
                            dcmd.Parameters["@LINE_TOTAL"].Value = decimal.Parse((alanlar[11]));
                            dcmd.Parameters["@LINE_NET_TOTAL"].Value = decimal.Parse(alanlar[12].Replace('.', ','));
                            dcmd.Parameters["@CURRENCY"].Value = int.Parse(alanlar[13]);
                            dcmd.Parameters["@CURRENCY_RATE"].Value = decimal.Parse((alanlar[14]).Replace('.', ','));
                            dcmd.Parameters["@TEVKIFAT_TURU"].Value = int.Parse(alanlar[15]);
                            dcmd.Parameters["@TEVKIFAT_TUTARI"].Value = decimal.Parse(alanlar[16].Replace('.', ','));
                            dcmd.Parameters["@OTV_TUTARI"].Value = decimal.Parse(alanlar[17].Replace('.', ','));
                            dcmd.ExecuteNonQuery();
                        }
                    }
                }
                else if (headerLines[1] == "30" || headerLines[1] == "31") //tahsilat ise
                {
                    dcmd.CommandText = "if exists(SELECT     HEADER_ID  FROM         RECEIVING WHERE     (HEADER_ID = @HEADER_ID) AND (LINE_NUMBER = @LINE_NUMBER)) update RECEIVING SET ID = @ID, DOCUMENT_TYPE = @DOCUMENT_TYPE, CARD_NO = @CARD_NO, DRAFT_TYPE = @DRAFT_TYPE, BANK = @BANK, BRANCH = @BRANCH, DRAW_DATE = @DRAW_DATE, DRAWER = @DRAWER, DRAFT_NO = @DRAFT_NO, ACCOUNT_CODE = @ACCOUNT_CODE, TOTAL = @TOTAL WHERE  (HEADER_ID = @HEADER_ID) AND (LINE_NUMBER = @LINE_NUMBER)  else insert into RECEIVING (ID, HEADER_ID, LINE_NUMBER, DOCUMENT_TYPE, CARD_NO, DRAFT_TYPE, BANK, BRANCH, DRAW_DATE, DRAWER, DRAFT_NO, ACCOUNT_CODE, TOTAL) VALUES (@ID, @HEADER_ID, @LINE_NUMBER, @DOCUMENT_TYPE, @CARD_NO, @DRAFT_TYPE, @BANK, @BRANCH, @DRAW_DATE, @DRAWER, @DRAFT_NO, @ACCOUNT_CODE, @TOTAL)";
                    dcmd.CommandType = CommandType.Text;
                    dcmd.Parameters.Add("@ID", SqlDbType.Int);
                    dcmd.Parameters.Add("@HEADER_ID", SqlDbType.Int).Value = header_id;
                    dcmd.Parameters.Add("@LINE_NUMBER", SqlDbType.SmallInt);
                    dcmd.Parameters.Add("@DOCUMENT_TYPE", SqlDbType.TinyInt);
                    dcmd.Parameters.Add("@CARD_NO", SqlDbType.NVarChar, 25);
                    dcmd.Parameters.Add("@DRAFT_TYPE", SqlDbType.TinyInt);
                    dcmd.Parameters.Add("@BANK", SqlDbType.NVarChar, 25);
                    dcmd.Parameters.Add("@BRANCH", SqlDbType.NVarChar, 25);
                    dcmd.Parameters.Add("@DRAW_DATE", SqlDbType.DateTime);
                    dcmd.Parameters.Add("@DRAWER", SqlDbType.NVarChar, 50);
                    dcmd.Parameters.Add("@DRAFT_NO", SqlDbType.NVarChar, 25);
                    dcmd.Parameters.Add("@ACCOUNT_CODE", SqlDbType.NVarChar, 25);
                    dcmd.Parameters.Add("@TOTAL", SqlDbType.Float);

                    string[] alanlar;
                    for (int i = 0; i < detailLines.Length; i++)
                    {
                        byte doc_type = 0;
                        alanlar = detailLines[i].Split(';');
                        if (alanlar.Length == 12)
                        {
                            dcmd.Parameters["@ID"].Value = int.Parse(alanlar[0]); //id
                            dcmd.Parameters["@LINE_NUMBER"].Value = i.ToString(); //satır
                            dcmd.Parameters["@DOCUMENT_TYPE"].Value = alanlar[2]; //işlem cinsi
                            dcmd.Parameters["@CARD_NO"].Value = alanlar[3].ToString() == "null" ? " " : alanlar[3].ToString(); //kart no
                            dcmd.Parameters["@DRAFT_TYPE"].Value = byte.Parse(alanlar[4].ToString() == "null" ? "0" : alanlar[4].ToString()); //çek tipi
                            dcmd.Parameters["@BANK"].Value = alanlar[5].ToString() == "null" ? "" : alanlar[5].ToString(); // banka
                            dcmd.Parameters["@BRANCH"].Value = alanlar[6].ToString() == "null" ? " " : alanlar[6].ToString(); //şube
                            dcmd.Parameters["@DRAW_DATE"].Value = DateTime.Parse(alanlar[7]); //keşide tarihi
                            dcmd.Parameters["@DRAWER"].Value = alanlar[8].ToString() == "null" ? " " : alanlar[8].ToString(); //keşideci
                            dcmd.Parameters["@DRAFT_NO"].Value = alanlar[9].ToString() == "null" ? " " : alanlar[9].ToString(); //çek no
                            dcmd.Parameters["@ACCOUNT_CODE"].Value = alanlar[10] == "null" ? " " : alanlar[10].ToString(); //hesap no
                            dcmd.Parameters["@TOTAL"].Value = decimal.Parse((alanlar[11])); // meblag
                            dcmd.ExecuteNonQuery();
                        }
                    }
                }
                try
                {
                    trn.Commit();
                    cn.Close();
                    AktarimSonlandir(header_id);
                }
                catch (Exception ex)
                {
                    trn.Rollback();
                    if (cn.State == ConnectionState.Open)
                        cn.Close();
                    return "Hata : " + ex.Message;
                }
            }
            return "true";
        }

        public void AktarimSonlandir(int headerId)
        {
            string sql = "UPDATE    INVOICE_HEADER SET SENT_FLAG = 1 WHERE     (ID = @hid) ";
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.Add("@hid", SqlDbType.Int).Value = headerId;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public void SaveStockMovement(string[] detailLines)
        {
            string sql = "if exists(select ID from STOCK_MOVEMENT where TERMINAL_ID=@TERMINAL_ID and DOCUMENT_TYPE=@DOCUMENT_TYPE and DOCUMENT_SERIAL=@DOCUMENT_SERIAL and DOCUMENT_NUMBER=@DOCUMENT_NUMBER and LINE_NUMBER =@LINE_NUMBER) DELETE FROM STOCK_MOVEMENT where TERMINAL_ID=@TERMINAL_ID and DOCUMENT_TYPE=@DOCUMENT_TYPE and DOCUMENT_SERIAL=@DOCUMENT_SERIAL and DOCUMENT_NUMBER=@DOCUMENT_NUMBER and LINE_NUMBER=@LINE_NUMBER;insert into STOCK_MOVEMENT (CREATE_DATE,UPDATE_DATE,SAVED_FLAG,SENT_FLAG,RETURN_TYPE,TERMINAL_ID,DOCUMENT_TYPE,DOCUMENT_SERIAL,DOCUMENT_NUMBER,LINE_NUMBER,PRODUCT_CODE,AMOUNT,PRICE,VAT_RATE,DISCOUNT_TOTAL,LINE_TOTAL,LINE_NET_TOTAL,LOADING_STORE,STORE_ID,DOCUMENT_DATE,CUSTOMER_CODE,ADRES_NO,ISKONTO1,ISKONTO2,ISKONTO3,ISKONTO4,ISKONTO5) values (@CREATE_DATE,@UPDATE_DATE,@SAVED_FLAG,@SENT_FLAG,@RETURN_TYPE,@TERMINAL_ID,@DOCUMENT_TYPE,@DOCUMENT_SERIAL,@DOCUMENT_NUMBER,@LINE_NUMBER,@PRODUCT_CODE,@AMOUNT,@PRICE,@VAT_RATE,@DISCOUNT_TOTAL,@LINE_TOTAL,@LINE_NET_TOTAL,@LOADING_STORE,@STORE_ID,@DOCUMENT_DATE,@CUSTOMER_CODE,@ADRES_NO,@ISKONTO1,@ISKONTO2,@ISKONTO3,@ISKONTO4,@ISKONTO5)";
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                cn.Open();

                string[] line1 = detailLines[0].Split(';');
                SqlTransaction trn = cn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(sql, cn);

                SqlCommand cmd1 = new SqlCommand("SELECT     PARAMETER_VALUE FROM         DEVICE_PARAMETERS WHERE     (PARAMETER_CODE = 'Store') AND (DEVICE_ID = " + line1[0] + ")", cn);
                cmd1.Transaction = trn;
                string depoKod = cmd1.ExecuteScalar().ToString();

                SqlCommand cm = new SqlCommand("SELECT     dep_RECno FROM         DEPOLAR WHERE     dep_no = '" + depoKod + "'", cn);
                cm.Transaction = trn;
                int depoID = Convert.ToInt32(cm.ExecuteScalar());

                cmd.Transaction = trn;
                cmd.Parameters.Add("@CREATE_DATE", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@UPDATE_DATE", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@SAVED_FLAG", SqlDbType.Bit).Value = false;
                cmd.Parameters.Add("@SENT_FLAG", SqlDbType.Bit).Value = true;
                cmd.Parameters.Add("@RETURN_TYPE", SqlDbType.Int);
                cmd.Parameters.Add("@TERMINAL_ID", SqlDbType.Int);
                cmd.Parameters.Add("@DOCUMENT_TYPE", SqlDbType.Int);
                cmd.Parameters.Add("@ADRES_NO", SqlDbType.Int);
                cmd.Parameters.Add("@DOCUMENT_SERIAL", SqlDbType.VarChar);
                cmd.Parameters.Add("@DOCUMENT_NUMBER", SqlDbType.Int);
                cmd.Parameters.Add("@LINE_NUMBER", SqlDbType.Int);
                cmd.Parameters.Add("@STORE_ID", SqlDbType.NVarChar, 25);
                cmd.Parameters.Add("@LOADING_STORE", SqlDbType.NVarChar, 25);
                cmd.Parameters.Add("@PRODUCT_CODE", SqlDbType.VarChar);
                cmd.Parameters.Add("@AMOUNT", SqlDbType.Float);
                cmd.Parameters.Add("@PRICE", SqlDbType.Float);
                cmd.Parameters.Add("@VAT_RATE", SqlDbType.Int);
                cmd.Parameters.Add("@DISCOUNT_TOTAL", SqlDbType.Float);
                cmd.Parameters.Add("@LINE_TOTAL", SqlDbType.Float);
                cmd.Parameters.Add("@LINE_NET_TOTAL", SqlDbType.Float);
                cmd.Parameters.Add("@DOCUMENT_DATE", SqlDbType.DateTime);
                cmd.Parameters.Add("@CUSTOMER_CODE", SqlDbType.VarChar);
                cmd.Parameters.Add("@ISKONTO1", SqlDbType.Float);
                cmd.Parameters.Add("@ISKONTO2", SqlDbType.Float);
                cmd.Parameters.Add("@ISKONTO3", SqlDbType.Float);
                cmd.Parameters.Add("@ISKONTO4", SqlDbType.Float);
                cmd.Parameters.Add("@ISKONTO5", SqlDbType.Float);

                for (int i = 0; i < detailLines.Length; i++)
                {
                    string[] line = detailLines[i].Split(';');
                    if (line.Length > 16)
                    {
                        cmd.Parameters["@RETURN_TYPE"].Value = Convert.ToInt32("0");
                        cmd.Parameters["@TERMINAL_ID"].Value = Convert.ToInt32(line[0]);
                        cmd.Parameters["@DOCUMENT_TYPE"].Value = Convert.ToInt32(line[1]);
                        cmd.Parameters["@DOCUMENT_SERIAL"].Value = line[3];
                        cmd.Parameters["@DOCUMENT_NUMBER"].Value = Convert.ToInt32(line[2]);
                        cmd.Parameters["@LINE_NUMBER"].Value = Convert.ToInt32(i);
                        cmd.Parameters["@STORE_ID"].Value = depoID;
                        cmd.Parameters["@LOADING_STORE"].Value = "";
                        cmd.Parameters["@PRODUCT_CODE"].Value = line[6];
                        cmd.Parameters["@AMOUNT"].Value = Convert.ToDecimal(line[7]);
                        cmd.Parameters["@PRICE"].Value = Convert.ToDecimal(line[8].Replace('.', ','));
                        cmd.Parameters["@VAT_RATE"].Value = Convert.ToInt32(line[14]);
                        cmd.Parameters["@DISCOUNT_TOTAL"].Value = Convert.ToDecimal(line[15]);
                        cmd.Parameters["@LINE_TOTAL"].Value = Convert.ToDecimal(line[16]);
                        cmd.Parameters["@LINE_NET_TOTAL"].Value = Convert.ToDecimal(line[17].Replace('.', ','));
                        cmd.Parameters["@DOCUMENT_DATE"].Value = DateTime.Now;
                        cmd.Parameters["@CUSTOMER_CODE"].Value = line[18];
                        cmd.Parameters["@ADRES_NO"].Value = "1";
                        cmd.Parameters["@ISKONTO1"].Value = Convert.ToDecimal(line[9]);
                        cmd.Parameters["@ISKONTO2"].Value = Convert.ToDecimal(line[10]);
                        cmd.Parameters["@ISKONTO3"].Value = Convert.ToDecimal(line[11]);
                        cmd.Parameters["@ISKONTO4"].Value = Convert.ToDecimal(line[12]);
                        cmd.Parameters["@ISKONTO5"].Value = Convert.ToDecimal(line[13]);
                        cmd.ExecuteNonQuery();
                    }

                }

                try
                {
                    trn.Commit();
                }
                catch (Exception)
                {
                    trn.Rollback();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public int MaxEvrakNo(int tip, string seri, byte programid)
        {
            int sira;
            string txtsql = string.Empty;
            switch (programid)
            {
                //Mikro
                case 0:
                    switch (tip)
                    {
                        case 0://satış faturası
                            txtsql = string.Format("exec sp_executesql N'SELECT     MAX(cha_evrakno_sira) + 1 FROM         CARI_HESAP_HAREKETLERI WHERE     (cha_tip = 0) AND (cha_cinsi = 6) AND (cha_normal_Iade = 0) AND (cha_evrak_tip = 63) AND (cha_evrakno_seri = ''{0}'')'", seri);
                            break;
                        case 1: //alınan sipariş
                            txtsql = string.Format("exec sp_executesql N'SELECT     MAX(sip_evrakno_sira) + 1 FROM         SIPARISLER  WHERE     (sip_tip = 0) AND (sip_cins = 0) AND (sip_evrakno_seri = ''{0}'')'", seri);
                            break;
                        case 2: //tahsilat
                            txtsql = string.Format("exec sp_executesql N'SELECT     MAX(cha_evrakno_sira) + 1 FROM         CARI_HESAP_HAREKETLERI WHERE     (cha_tip = 1) AND (cha_normal_Iade = 0) AND (cha_evrak_tip = 1) AND (cha_evrakno_seri = ''{0}'')'", seri);
                            break;
                        case 3: //iade
                            txtsql = string.Format("exec sp_executesql N'SELECT     MAX(cha_evrakno_sira) + 1 FROM         CARI_HESAP_HAREKETLERI WHERE     (cha_tip = 1) AND (cha_normal_Iade = 1) AND (cha_evrak_tip = 0) AND (cha_evrakno_seri = ''{0}'')'", seri);
                            break;
                        case 4: //depo sevk
                            txtsql = string.Format("exec sp_executesql N'SELECT     MAX(sth_evrakno_sira) + 1 FROM         STOK_HAREKETLERI  WHERE     (sth_tip = 2) AND (sth_cins = 6) AND (sth_evraktip = 2) AND (sth_evrakno_seri = ''{0}'')'", seri);
                            break;
                    }
                    break;
                //WorkGroup
                case 1:
                    switch (tip)
                    {
                        case 0://satış faturası
                            txtsql = string.Format("exec sp_executesql N'SELECT MAX(char_sira) + 1 FROM PW_001_001_CHAREKET WHERE (char_seri = N''{0}'')'", seri);
                            break;
                        case 1: //alınan sipariş
                            txtsql = string.Format("exec sp_executesql N'SELECT MAX(sip_sira) + 1 FROM PW_001_001_SIPARIS WHERE  (sip_seri = N''{0}'')'", seri);
                            break;
                        case 2: //tahsilat
                            txtsql = string.Format("exec sp_executesql N'SELECT MAX(char_sira) + 1 FROM PW_001_001_CHAREKET WHERE (char_seri = N''{0}'')'", seri);
                            break;
                        case 3: //iade
                            txtsql = string.Format("exec sp_executesql N'SELECT MAX(char_sira) + 1 FROM PW_001_001_CHAREKET WHERE (char_seri = N''{0}'')'", seri);
                            break;
                        case 4: //depo sevk
                            txtsql = string.Format("exec sp_executesql N'SELECT MAX(shar_sira) + 1 FROM PW_001_001_SHAREKET WHERE (shar_seri = N''{0}'')'", seri);
                            break;
                    }
                    break;
            }



            object obj = null;
            using (SqlConnection cn = new SqlConnection(ConStr))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(txtsql, cn))
                {
                    cmd.CommandTimeout = 9999;
                    obj = cmd.ExecuteScalar();
                }
                cn.Close();
            }

            if (obj != DBNull.Value && obj != null)
                sira = Convert.ToInt32(obj);
            else
                sira = 1;

            return sira;
        }

    }
}
